﻿Imports System.Data.SqlClient

Public Class FrmlapPhieuMuonBanDoDiaChinh
    Public m_tennhap As String
    Private Sub NapDonViHuyen()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TENHUYEN", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxHuyen.DisplayMember = "T_huyen"
        ComboBoxHuyen.ValueMember = "maH"
        ComboBoxHuyen.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapLoaiTaiLieu()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMTaiLieu", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxLoaiTaiLieu.DisplayMember = "TenLoaiTaiLieu"
        ComboBoxLoaiTaiLieu.ValueMember = "maLoaiTaiLieuID"
        ComboBoxLoaiTaiLieu.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapHinhThuc()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from DMHinhThuc", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxHinhThuc.DisplayMember = "TenHinhThuc"
        ComboBoxHinhThuc.ValueMember = "maHinhThucID"
        ComboBoxHinhThuc.DataSource = tbl
        ob_cnn.Close()
    End Sub

    Private Sub NapDataGridViewBanDo()
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand("select " &
                                "   MasoBD " &
                                "  ,NgayNhap " &
                                "  ,TongToBanDo " &
                                "  ,TongThua " &
                                "  ,TongDienTich " &
                                "  ,NamThanhLap " &
                                "  ,DonViDo " &
                                "  ,DiaDanh " &
                                "  ,ToBanDoSo " &
                                "  ,TenTyLe " &
                                "  ,ht.TenHienTrang " &
                                "  ,tli.TenLoaiTaiLieu " &
                                "  ,ke.tenke " &
                                "  ,k.TenKho " &
                                " From  BanDoDiaChinh dc, DMTyLe tl, DMHienTrang ht, DMTaiLieu tli, DMKho k, DMKe ke, tenxa tx, tenhuyen th " &
                                " WHERE dc.tyleid = tl.matyle " &
                                " and dc.MaLoaiTaiLieuID = tli.maloaiTaiLieuID " &
                                " and dc.makhoid = k.makhoid " &
                                " and dc.maKeid = ke.makeid " &
                                " and MaHuyenId = '" & ComboBoxHuyen.SelectedValue.ToString & "' " &
                                " and dc.mahuyenid = th.maH " &
                                " and MaXaId = '" & ComboBoxXa.SelectedValue.ToString & "' " &
                                " and dc.maxaid = tx.maxa " &
                                " and dc.maHienTrangID = ht.mahienTrang " &
                                " and dc.matinhtrangid = '1' ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridViewBanDo.DataSource = tbl
        If (tbl.Rows.Count = 0) Then
            MessageBox.Show("Không tìm thấy thông tin !")
        Else

        End If
        ob_cnn.Close()
    End Sub
    Private Sub NapBanDoVaoTextBox()
        TextBoxMasoBD.DataBindings.Clear()
        TextBoxMasoBD.DataBindings.Add("Text", DataGridViewBanDo.DataSource, "MasoBD")
        TextBoxToBanDo.DataBindings.Clear()
        TextBoxToBanDo.DataBindings.Add("Text", DataGridViewBanDo.DataSource, "tobandoso")
        TextBoxDiaDanh.DataBindings.Clear()
        TextBoxDiaDanh.DataBindings.Add("Text", DataGridViewBanDo.DataSource, "Diadanh")
        TextBoxNamThanhLap.DataBindings.Clear()
        TextBoxNamThanhLap.DataBindings.Add("Text", DataGridViewBanDo.DataSource, "NamThanhLap")
        TextBoxTyLe.DataBindings.Clear()
        TextBoxTyLe.DataBindings.Add("Text", DataGridViewBanDo.DataSource, "TenTyle")
        ComboBoxLoaiTaiLieu.DataBindings.Clear()
        ComboBoxLoaiTaiLieu.DataBindings.Add("Text", DataGridViewBanDo.DataSource, "TenLoaiTaiLieu")
        TextBoxKe.DataBindings.Clear()
        TextBoxKe.DataBindings.Add("Text", DataGridViewBanDo.DataSource, "TenKe")
        TextBoxKho.DataBindings.Clear()
        TextBoxKho.DataBindings.Add("Text", DataGridViewBanDo.DataSource, "TenKho")
    End Sub
    Private Sub NhapNguoiMuon()
        Try
            Dim tennhap As String
            tennhap = ModuleTenDangNhap.m_tennhap
            Dim NgayMuon As String
            NgayMuon = String.Format("{0:MM/dd/yyyy}", DateTimeNgayMuon.Value)
            Dim Sql As String = ""
            Sql = " INSERT INTO BanDoMuon (Ngaymuon, NamThanhLap, MaHuyenId, MaXaId,ToBanDoSo, " &
                "NguoiNhap, NgayNhap, MaLoaiTaiLieuID, DiaChi, NguoiMuon, DonViCongTac, MucDichMuon, mahinhthucID, MatinhtrangID, masoBD )" &
           "  VALUES" &
           " ('" & NgayMuon & "' ," &
           " '" & TextBoxNamThanhLap.Text & "' ," &
            " '" & ComboBoxHuyen.SelectedValue.ToString & "', " &
            " '" & ComboBoxXa.SelectedValue.ToString & "', " &
            " '" & TextBoxToBanDo.Text & "', " &
            " N'" & tennhap & "', " &
            " " & Format(Now.ToOADate() - 2) & ", " &
            " '" & ComboBoxLoaiTaiLieu.SelectedValue.ToString & "' , " &
            " N'" & TextBoxDiaChi.Text & "', " &
            " N'" & TextBoxNguoiMuon.Text & "', " &
            " N'" & TextBoxDonViMuon.Text & "', " &
            " N'" & TextBoxMucDich.Text & "', " &
            " '" & ComboBoxHinhThuc.SelectedValue.ToString & "', " &
            " '2'," &
            " '" & TextBoxMasoBD.Text & "')"
            Dim cmd As New SqlCommand(Sql, ob_cnn)
            ob_cnn.Open()
            cmd.ExecuteNonQuery()
            ob_cnn.Close()
            MsgBox("Thêm thông tin thành công!")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Private Sub CapNhatDulieu()
        Try
            Dim Sql As String = ""
            Sql = " Update BanDoDiaChinh Set " &
                                " MatinhtrangID  =  '2' " &
                                ", MaSoMuon = '" & TextBoxMasoID.Text & "'" &
                                " where MasoBD = '" & TextBoxMasoBD.Text & "'"
            Dim cmd As New SqlCommand(Sql, ob_cnn)
            ob_cnn.Open()
            cmd.ExecuteNonQuery()
            ob_cnn.Close()

            NapDataGridViewBanDo()

        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try
    End Sub
    Public Sub NapDuLieuNguoiMuon()
        DataGridViewNguoiMuon.Rows.Add(DateTimeNgayMuon.Text, TextBoxNguoiMuon.Text, TextBoxDonViMuon.Text, TextBoxDiaChi.Text, TextBoxMucDich.Text)
    End Sub
    Private Sub FrmlapPhieuMuonBanDoDiaChinh_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        DataGridViewBanDo.RowsDefaultCellStyle.BackColor = Color.LightBlue
        DataGridViewBanDo.AlternatingRowsDefaultCellStyle.BackColor = Color.Azure
        DataGridViewNguoiMuon.RowsDefaultCellStyle.BackColor = Color.LightBlue
        DataGridViewNguoiMuon.AlternatingRowsDefaultCellStyle.BackColor = Color.AntiqueWhite
        NapDonViHuyen()
        NapLoaiTaiLieu()
        NapHinhThuc()
        '   NapDataGridViewBanDo()
        TextBoxMasoID.Text = checkHoSoMuon() + 1
        ButtonInPhieu.Enabled = False

    End Sub
    Public Function checkHoSoMuon()

        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim sqCom As SqlCommand
        sqCom = New SqlCommand("select * from bandomuon ", ob_cnn)
        dbAdapter = New SqlDataAdapter(sqCom)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        '  DataGridViewNguoiMuon.DataSource = tbl

        If (tbl.Rows.Count = 0) Then
            Dim Sql As String = ""
            Sql = " INSERT INTO BanDoMuon (MasoBD)" &
           "  VALUES" &
           " ('0')"
            Dim cmd As New SqlCommand(Sql, ob_cnn)
            ob_cnn.Open()
            cmd.ExecuteNonQuery()
            ob_cnn.Close()
            'MessageBox.Show("Không tìm thấy thông tin !")
        Else

        End If
        ob_cnn.Open()
        Dim sqlCom As New SqlCommand("select max(masoBD) from BanDoMuon ", ob_cnn)
        Dim max As Int32 = Convert.ToInt32(sqlCom.ExecuteScalar())
        ob_cnn.Close()
        Return max

    End Function

    Private Sub ComboBoxHuyen_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBoxHuyen.SelectedIndexChanged
        ' load ten huyen
        Dim tbl As DataTable
        Dim dbAdapter As SqlDataAdapter
        Dim dbComm As SqlCommand
        dbComm = New SqlCommand(" select * from TenXa where mahuyen = '" & ComboBoxHuyen.SelectedValue.ToString & "' ", ob_cnn)
        dbAdapter = New SqlDataAdapter(dbComm)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxXa.DisplayMember = "TenXa"
        ComboBoxXa.ValueMember = "maxa"
        ComboBoxXa.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub DataGridViewBanDo_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewBanDo.CellClick
        NapBanDoVaoTextBox()
    End Sub

    Private Sub LamTuoi()
        TextBoxDiaChi.Text = ""
        TextBoxDiaDanh.Text = ""
        TextBoxDonViMuon.Text = ""
        TextBoxMasoBD.Text = ""
        TextBoxMucDich.Text = ""
        TextBoxNamThanhLap.Text = ""
        TextBoxNguoiMuon.Text = ""
        TextBoxToBanDo.Text = ""
        TextBoxTyLe.Text = ""
        TextBoxKe.Text = ""
        TextBoxKho.Text = ""
    End Sub
    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        If TextBoxNguoiMuon.Text = "" Then
            MessageBox.Show(" Thông tin người mượn không được rỗng")
            Return
        End If
        If TextBoxDonViMuon.Text = "" Then
            MessageBox.Show("Đơn vị công tác không được rỗng")
            Return
        End If
        If TextBoxDiaChi.Text = "" Then
            MessageBox.Show("Địa chỉ không được rỗng")
            Return
        End If
        If TextBoxMucDich.Text = "" Then
            MessageBox.Show("Mục đích mượn không được rỗng")
            Return
        Else
            NhapNguoiMuon()
            NapDuLieuNguoiMuon()
            CapNhatDulieu()
            TextBoxMasoID.Text = checkHoSoMuon() + 1
            '  LamTuoi()
            ButtonInPhieu.Enabled = True
        End If

    End Sub

    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        Me.Close()

    End Sub

    Private Sub DataGridViewBanDo_RowPostPaint(sender As Object, e As DataGridViewRowPostPaintEventArgs) Handles DataGridViewBanDo.RowPostPaint
        Dim strRowNumber As String = (e.RowIndex + 1).ToString
        While (strRowNumber.Length < DataGridViewBanDo.RowCount.ToString.Length)
            strRowNumber = "0" & strRowNumber
        End While
        Dim Size As SizeF = e.Graphics.MeasureString(strRowNumber, MyBase.Font)
        If DataGridViewBanDo.RowHeadersWidth < Size.Width + 20 Then DataGridViewBanDo.RowHeadersWidth = Size.Width + 20
        Dim b As Brush = SystemBrushes.ControlText
        e.Graphics.DrawString(strRowNumber, MyBase.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y +
                              ((e.RowBounds.Height - Size.Height) / 2))

    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        NapDataGridViewBanDo()
    End Sub

    Private Sub ButtonInPhieu_Click(sender As Object, e As EventArgs) Handles ButtonInPhieu.Click
        If TextBoxMasoID.Text = Nothing Then
            MessageBox.Show("Chọn hồ sơ cần thao tác!")
            Return
        End If
        Dim ts() As String = {"<SoPhieuMuon>", "<NguoiMuon>", "<DonVi>", "<DiaChi>", "<SoHoSo>", "<ToBanDo>", "<ThuocHuyen>", "<ThuocXa>", "<TenKho>", "<TenKe>", "<MucDichMuon>", "<HinhThuc>", "<NgayMuon>", "<NguoiNhap>"}
        Dim gt() As String = {AutoID("BanDoMuon"), TextBoxNguoiMuon.Text, TextBoxDonViMuon.Text, TextBoxDiaChi.Text, TextBoxMasoID.Text, TextBoxToBanDo.Text, ComboBoxHuyen.Text.Trim, ComboBoxXa.Text.Trim, TextBoxKho.Text.Trim, TextBoxKe.Text.Trim, TextBoxMucDich.Text, ComboBoxHinhThuc.Text.ToString, DateTimeNgayMuon.Text, GetTenNguoiNhap(ModuleTenDangNhap.m_tennhap)}
        ExportWord(ts, gt, "PhieuMuonBanDoDiaChinh", AutoID("BanDoMuon"))

    End Sub

    Private Sub TextBoxMaID_TextChanged(sender As Object, e As EventArgs) Handles TextBoxMasoBD.TextChanged

    End Sub

    Private Sub DataGridViewBanDo_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridViewBanDo.CellContentClick

    End Sub
End Class